﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO = Microsoft.MSE.Catalog.Contracts;
using System.Xml;

namespace Microsoft.MSE.Catalog.Module.Data
{
    public class OperationVersion 
    {
       
        public static Guid CreateOperationVersion(DTO.OperationVersion version, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            Guid implementationID = Guid.Empty;
            const string sprocname = "[ServiceEngine].[CreateOperationVersion]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter OperationIDParam = new SqlParameter("@OperationID", SqlDbType.UniqueIdentifier);
                    SqlParameter VersionMajorParam = new SqlParameter("@VersionMajor", SqlDbType.Int);
                    SqlParameter VersionMinorParam = new SqlParameter("@VersionMinor", SqlDbType.Int);
                    SqlParameter VersionBuildParam = new SqlParameter("@VersionBuild", SqlDbType.Int);
                    SqlParameter VersionQFEParam = new SqlParameter("@VersionQFE", SqlDbType.Int);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter AutoResponseParam = new SqlParameter("@AutoResponse", SqlDbType.Xml);
                    SqlParameter IsPublicParam = new SqlParameter("@IsPublic", SqlDbType.Bit);
                    SqlParameter IsActiveParam = new SqlParameter("@IsActive", SqlDbType.Bit);
                    SqlParameter IsTestableParam = new SqlParameter("@IsTestable", SqlDbType.Bit);
                    SqlParameter RequestTransformParam = new SqlParameter("@RequestTransform", SqlDbType.Xml);
                    SqlParameter ResponseTransformParam = new SqlParameter("@ResponseTransform", SqlDbType.Xml);
                    SqlParameter ResourceIDParam = new SqlParameter("@ResourceID", SqlDbType.UniqueIdentifier);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    OperationIDParam.Value = version.OperationID;
                    VersionMajorParam.Value = int.Parse(version.Version.Major);
                    VersionMinorParam.Value = int.Parse(version.Version.Minor);
                    VersionBuildParam.Value = int.Parse(version.Version.Build);
                    VersionQFEParam.Value = int.Parse(version.Version.Qfe);
                    DescriptionParam.Value = version.Description;                    

                    if (version.AutoResponse != null)
                    {
                        AutoResponseParam.Value = version.AutoResponse.OuterXml;
                    }
                    else
                    {
                        AutoResponseParam.Value = DBNull.Value;
                    }
                    IsPublicParam.Value = version.IsPublic;
                    IsActiveParam.Value = version.IsActive;
                    IsTestableParam.Value = version.IsTestable;
                    if (version.RequestTransformXSLT != null)
                    {
                        RequestTransformParam.Value = version.RequestTransformXSLT.OuterXml;
                    }
                    else
                    {
                        RequestTransformParam.Value = DBNull.Value;
                    }
                    if (version.ResponseTransformXSLT != null)
                    {
                        ResponseTransformParam.Value = version.ResponseTransformXSLT.OuterXml;
                    }
                    else
                    {
                        ResponseTransformParam.Value = DBNull.Value;
                    }
                    if (version.Resource != null)
                    {
                        if (version.Resource.ID.Equals(Guid.Empty))
                        {
                            ResourceIDParam.Value = Resource.CreateResource(version.Resource, txn, conn);
                        }
                        else
                        {
                            ResourceIDParam.Value = version.Resource.ID;
                        }
                    }
                    else
                    {
                        ResourceIDParam.Value = null;
                        //throw new ApplicationException("CreateOperationVersion requires ChannelType, but no ChannelType was supplied");
                    }

                    if (version.Policy != null)
                    {
                        PolicyIDParam.Value = version.Policy.ID;
                    }
                    else
                    {
                        PolicyIDParam.Value = DBNull.Value;
                    }                    

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(OperationIDParam);
                    cmd.Parameters.Add(VersionMajorParam);
                    cmd.Parameters.Add(VersionMinorParam);
                    cmd.Parameters.Add(VersionBuildParam);
                    cmd.Parameters.Add(VersionQFEParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(AutoResponseParam);
                    cmd.Parameters.Add(IsPublicParam);
                    cmd.Parameters.Add(IsActiveParam);
                    cmd.Parameters.Add(IsTestableParam);
                    cmd.Parameters.Add(RequestTransformParam);
                    cmd.Parameters.Add(ResponseTransformParam);
                    cmd.Parameters.Add(ResourceIDParam);
                    cmd.Parameters.Add(PolicyIDParam);

                    cmd.ExecuteNonQuery();
                    ID = (Guid)IDParam.Value;
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return ID;
        }

        public static void DeleteOperationVersion(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteOperationVersion]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

        }

        public static DTO.OperationVersion GetOperationVersion(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.OperationVersion version = new DTO.OperationVersion();
            const string sprocname = "[ServiceEngine].[GetOperationVersion]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            version.ID = reader.GetGuid(0);
                            version.OperationID = reader.GetGuid(1);
                            version.OperationName = reader.GetString(2);
                            DTO.VersionNumber verNumber = new DTO.VersionNumber();
                            verNumber.Major = reader.GetInt32(3).ToString();
                            verNumber.Minor = reader.GetInt32(4).ToString();
                            verNumber.Build = reader.GetInt32(5).ToString();
                            verNumber.Qfe = reader.GetInt32(6).ToString();
                            version.Version = verNumber;
                            if (reader.IsDBNull(7))
                            {
                                version.AutoResponse = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(7).CreateReader());
                                version.AutoResponse = xdoc.DocumentElement;

                            }
                            version.IsPublic = reader.GetBoolean(8);
                            version.IsActive = reader.GetBoolean(9);
                            version.IsTestable = reader.GetBoolean(10);
                            version.RequestMsg = MessagePart.GetMessage(version.ID, SC.IsRequestMsg, txn, conn);
                            version.ResponseMsg = MessagePart.GetMessage(version.ID, SC.IsResponseMsg, txn, conn);

                            if (reader.IsDBNull(11))
                            {
                                version.RequestTransformXSLT = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(11).CreateReader());
                                version.RequestTransformXSLT = xdoc.DocumentElement;
                            }

                            if (reader.IsDBNull(12))
                            {
                                version.ResponseTransformXSLT = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(12).CreateReader());
                                version.ResponseTransformXSLT = xdoc.DocumentElement;
                            }
                            
                            if (reader.IsDBNull(14))
                            {
                                version.Description = null;
                            }
                            else
                            {
                                version.Description = reader.GetString(14);
                            }

                            if (reader.IsDBNull(15))
                            {
                                version.Resource = null;
                            }
                            else
                            {
                                DTO.Resource res = Resource.GetResource(reader.GetGuid(15), false, null, txn, conn);
                                version.Resource = res;
                            }

                            if (reader.IsDBNull(16))
                            {
                                version.Policy = null;
                            }
                            else
                            {
                                version.Policy = CatalogSvcHelper.AssemblePolicyFromPolicyAssertion(reader.GetGuid(16), txn, conn);                                
                            }

                            // Get SoapFaults
                            version.SoapFaults = SoapFault.GetSoapFaults(ID, txn, conn);

                            version.RequestHeaders = MessageHeader.GetMessageHeaders(version.ID, SC.IsRequestMsg, txn, conn);
                            version.ResponseHeaders = MessageHeader.GetMessageHeaders(version.ID, SC.IsResponseMsg, txn, conn);

                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return version;

        }

        public static List<DTO.OperationVersion> GetOperationVersionsForEndpointAndAction(Guid endpointID, string action, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.OperationVersion> response = new List<DTO.OperationVersion>();
            const string sprocname = "[ServiceEngine].[GetOperationVersionsForEndpointAndAction]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);
                    SqlParameter OperationNameParam = new SqlParameter("@OperationName", SqlDbType.NVarChar, 4000);
                    SqlParameter VersionMajorParam = new SqlParameter("@VersionMajor", SqlDbType.Int);
                    SqlParameter VersionMinorParam = new SqlParameter("@VersionMinor", SqlDbType.Int);
                    SqlParameter VersionBuildParam = new SqlParameter("@VersionBuild", SqlDbType.Int);
                    SqlParameter VersionQFEParam = new SqlParameter("@VersionQFE", SqlDbType.Int);

                    cmd.CommandType = CommandType.StoredProcedure;
                    EndpointIDParam.Value = endpointID;
                    OperationNameParam.Value = DBNull.Value;
                    VersionMajorParam.Value = DBNull.Value;
                    VersionMinorParam.Value = DBNull.Value;
                    VersionBuildParam.Value = DBNull.Value;
                    VersionQFEParam.Value = DBNull.Value;

                    // Parse Action into subfields
                    if (!string.IsNullOrEmpty(action))
                    {
                        string[] tokens = action.Split(new char[] { ':' });
                        if (tokens.Length > 2) throw new ApplicationException(string.Format("GetOperationVersionsForEndpointAndAction failed to parse Action [{0}].  Expected no more than two sections, found [{1}]", action, tokens.Length));
                        if (tokens.Length == 1)
                        { // Endpoint and Operation Name
                            OperationNameParam.Value = tokens[0];
                            VersionMajorParam.Value = DBNull.Value;
                            VersionMinorParam.Value = DBNull.Value;
                            VersionBuildParam.Value = DBNull.Value;
                            VersionQFEParam.Value = DBNull.Value;
                        }
                        else
                        {
                            if (tokens.Length == 2)
                            { // Endpoint, Operation Name and Version
                                OperationNameParam.Value = tokens[0];
                                SetVersionParams(tokens[1], VersionMajorParam, VersionMinorParam, VersionBuildParam, VersionQFEParam);
                            }
                        }
                    }

                    cmd.Parameters.Add(EndpointIDParam);
                    cmd.Parameters.Add(OperationNameParam);
                    cmd.Parameters.Add(VersionMajorParam);
                    cmd.Parameters.Add(VersionMinorParam);
                    cmd.Parameters.Add(VersionBuildParam);
                    cmd.Parameters.Add(VersionQFEParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            DTO.OperationVersion version = new DTO.OperationVersion();

                            version.ID = reader.GetGuid(0);
                            DTO.VersionNumber verNumber = new DTO.VersionNumber();
                            verNumber.Major = reader.GetInt32(1).ToString();
                            verNumber.Minor = reader.GetInt32(2).ToString();
                            verNumber.Build = reader.GetInt32(3).ToString();
                            verNumber.Qfe = reader.GetInt32(4).ToString();
                            version.Version = verNumber;
                            if (reader.IsDBNull(5))
                            {
                                version.AutoResponse = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(5).CreateReader());
                                version.AutoResponse = xdoc.DocumentElement;

                            }
                            //rcamacho - version.OperationName was missing
                            version.OperationName = OperationNameParam.Value.ToString();
                            version.IsTestable = reader.GetBoolean(6);
                            version.IsActive = reader.GetBoolean(7);
                            version.RequestMsg = MessagePart.GetMessage(version.ID, SC.IsRequestMsg, txn, conn);
                            version.ResponseMsg = MessagePart.GetMessage(version.ID, SC.IsResponseMsg, txn, conn);

                            if (reader.IsDBNull(8))
                            {
                                version.RequestTransformXSLT = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(8).CreateReader());
                                version.RequestTransformXSLT = xdoc.DocumentElement;
                            }

                            if (reader.IsDBNull(9))
                            {
                                version.ResponseTransformXSLT = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(9).CreateReader());
                                version.ResponseTransformXSLT = xdoc.DocumentElement;
                            }

                            if (reader.IsDBNull(13))
                            {
                                version.Policy = null;
                            }
                            else
                            {
                                DTO.Policy pol = Policy.GetPolicy(reader.GetGuid(13), txn, conn);
                                version.Policy = pol;
                            }

                            if (reader.IsDBNull(12))
                            {
                                version.Resource = null;
                            }
                            else
                            {
                                DTO.Resource res = Resource.GetResource(reader.GetGuid(12), true, version.Policy, txn, conn);
                                // This method is only used by Runtime
                                // Clear the IDL in order to reduce message payload
                                // as this is not used by Runtime
                                res.IDL = "";
                                version.Resource = res;
                                //need to get build up the address for the moniker - only needed by Runtime

                                if (!res.Moniker.HasAttribute("Address"))
                                {
                                    string thisMoniker = "";
                                    DTO.SystemInstance ins = MSESystemInstance.GetSystemInstance(res.InstanceID, txn, conn);                                
                                    DTO.System sys = MSESystem.GetSystem(ins.SystemID, txn, conn);
                                    System.ServiceModel.Channels.Binding resBn = BindingHelper.DeserializeBinding(res.Binding.BindingConfig);
                                    if (resBn.Scheme != null)
                                        thisMoniker += resBn.Scheme + "://";
                                    if (sys.URI != null)
                                        thisMoniker += sys.URI;
                                    if (ins.Port > 0)
                                        thisMoniker += ":" + ins.Port;
                                    if (ins.URI != null)
                                        thisMoniker += ins.URI;
                                    if (res.URI != null)
                                        thisMoniker += res.URI;
                                                              
                                    XmlElement moniker = res.Moniker;
                                    moniker.SetAttributeNode("Address", null);
                                    moniker.SetAttribute("Address", thisMoniker);
                                    res.Moniker = moniker;
                                }
                            }


                            version.OperationID = reader.GetGuid(11);
                            // Get SoapFaults
                            version.SoapFaults = SoapFault.GetSoapFaults(version.ID, txn, conn);
                            
                            version.RequestHeaders = MessageHeader.GetMessageHeaders(version.ID, SC.IsRequestMsg, txn, conn);
                            version.ResponseHeaders = MessageHeader.GetMessageHeaders(version.ID, SC.IsResponseMsg, txn, conn);

                            response.Add(version);
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            if (response.Count == 0)
            {
                throw new ApplicationException(string.Format("No Active Operations exist on this endpoint that are identified by the Action parameter {0}", action));
            }

            return response;

        }

        public static List<DTO.OperationVersion> GetOperationVersionsForEndpointAndFirstBodyElement(Guid endpointID, string elementName, string namespaceURI, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.OperationVersion> response = new List<DTO.OperationVersion>();
            const string sprocname = "[ServiceEngine].[GetOperationVersionsForEndpointAndFirstBodyElement]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);
                    SqlParameter ElementNameParam = new SqlParameter("@ElementName", SqlDbType.NVarChar, 400);
                    SqlParameter namespaceURIParam = new SqlParameter("@namespaceURI", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    EndpointIDParam.Value = endpointID;
                    ElementNameParam.Value = elementName;
                    namespaceURIParam.Value = namespaceURI;

                    cmd.Parameters.Add(EndpointIDParam);
                    cmd.Parameters.Add(ElementNameParam);
                    cmd.Parameters.Add(namespaceURIParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            DTO.OperationVersion version = new DTO.OperationVersion();
                            version.ID = reader.GetGuid(0);
                            DTO.VersionNumber verNumber = new DTO.VersionNumber();
                            verNumber.Major = reader.GetInt32(1).ToString();
                            verNumber.Minor = reader.GetInt32(2).ToString();
                            verNumber.Build = reader.GetInt32(3).ToString();
                            verNumber.Qfe = reader.GetInt32(4).ToString();
                            version.Version = verNumber;
                            if (reader.IsDBNull(5))
                            {
                                version.AutoResponse = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(5).CreateReader());
                                version.AutoResponse = xdoc.DocumentElement;

                            }
                            version.IsTestable = reader.GetBoolean(6);
                            version.IsActive = reader.GetBoolean(7);
                            version.RequestMsg = MessagePart.GetMessage(version.ID, SC.IsRequestMsg, txn, conn);
                            version.ResponseMsg = MessagePart.GetMessage(version.ID, SC.IsResponseMsg, txn, conn);

                            if (reader.IsDBNull(8))
                            {
                                version.RequestTransformXSLT = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(8).CreateReader());
                                version.RequestTransformXSLT = xdoc.DocumentElement;
                            }

                            if (reader.IsDBNull(9))
                            {
                                version.ResponseTransformXSLT = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(9).CreateReader());
                                version.ResponseTransformXSLT = xdoc.DocumentElement;
                            }

                            if (reader.IsDBNull(13))
                            {
                                version.Policy = null;
                            }
                            else
                            {
                                DTO.Policy pol = Policy.GetPolicy(reader.GetGuid(13), txn, conn);
                                version.Policy = pol;
                            }

                            if (reader.IsDBNull(12))
                            {
                                version.Resource = null;
                            }
                            else
                            {
                                DTO.Resource res = Resource.GetResource(reader.GetGuid(12), true, version.Policy, txn, conn);
                                // This method is only used by Runtime
                                // Clear the IDL in order to reduce message payload
                                // as this is not used by Runtime
                                res.IDL = "";
                                version.Resource = res;

                                if (!res.Moniker.HasAttribute("Address"))
                                {
                                    string thisMoniker = "";
                                    DTO.SystemInstance ins = MSESystemInstance.GetSystemInstance(res.InstanceID, txn, conn);
                                    DTO.System sys = MSESystem.GetSystem(ins.SystemID, txn, conn);
                                    System.ServiceModel.Channels.Binding resBn = BindingHelper.DeserializeBinding(res.Binding.BindingConfig);
                                    if (resBn.Scheme != null)
                                        thisMoniker += resBn.Scheme + "://";
                                    if (sys.URI != null)
                                        thisMoniker += sys.URI;
                                    if (ins.Port > 0)
                                        thisMoniker += ":" + ins.Port;
                                    if (ins.URI != null)
                                        thisMoniker += ins.URI;
                                    if (res.URI != null)
                                        thisMoniker += res.URI;

                                    XmlElement moniker = res.Moniker;
                                    moniker.SetAttributeNode("Address", null);
                                    moniker.SetAttribute("Address", thisMoniker);
                                    res.Moniker = moniker;
                                }
                            }

                            version.OperationID = reader.GetGuid(11);
                            // Get SoapFaults
                            version.SoapFaults = SoapFault.GetSoapFaults(version.ID, txn, conn);

                            version.RequestHeaders = MessageHeader.GetMessageHeaders(version.ID, SC.IsRequestMsg, txn, conn);
                            version.ResponseHeaders = MessageHeader.GetMessageHeaders(version.ID, SC.IsResponseMsg, txn, conn);
                            
                            response.Add(version);
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            if (response.Count == 0)
            {
                throw new ApplicationException(string.Format("No Active Operations exist on this endpoint with the first body element of {0} in namespace {1}", elementName, namespaceURI));
            }

            return response;

        }

        public static DTO.OperationVersionList ListOperationVersions(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.OperationVersionList list = new DTO.OperationVersionList();
            const string sprocname = "[ServiceEngine].[ListOperationVersions]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@OperationID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.OperationVersions = new List<DTO.OperationVersionListElement>();
                            while (reader.Read())
                            {
                                DTO.OperationVersionListElement element = new DTO.OperationVersionListElement();
                                element.Version = new DTO.VersionNumber();
                                element.ID = reader.GetGuid(0);
                                DTO.VersionNumber verNumber = new DTO.VersionNumber();
                                verNumber.Major = reader.GetInt32(1).ToString();
                                verNumber.Minor = reader.GetInt32(2).ToString();
                                verNumber.Build = reader.GetInt32(3).ToString();
                                verNumber.Qfe = reader.GetInt32(4).ToString();
                                element.Version = verNumber;
                                list.OperationVersions.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.OperationVersionList ListOperationVersionsBySchema(Guid SchemaID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.OperationVersionList list = new DTO.OperationVersionList();
            const string sprocname = "[ServiceEngine].[ListOperationVersionsBySchema]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@SchemaID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = SchemaID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.OperationVersions = new List<DTO.OperationVersionListElement>();
                            while (reader.Read())
                            {
                                DTO.OperationVersionListElement element = new DTO.OperationVersionListElement();
                                element.Version = new DTO.VersionNumber();
                                element.ID = reader.GetGuid(0);
                                DTO.VersionNumber verNumber = new DTO.VersionNumber();
                                verNumber.Major = reader.GetInt32(1).ToString();
                                verNumber.Minor = reader.GetInt32(2).ToString();
                                verNumber.Build = reader.GetInt32(3).ToString();
                                verNumber.Qfe = reader.GetInt32(4).ToString();
                                element.Version = verNumber;
                                list.OperationVersions.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static List<DTO.Entity> ListOperationVersionsBySchemaObject(Guid SchemaObjectID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.Entity> opVerList = new List<DTO.Entity>();
            const string sprocname = "[ServiceEngine].[ListOperationVersionsBySchemaObject]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@SchemaObjectID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = SchemaObjectID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                DTO.Entity element = new DTO.Entity();
                                element.ID = reader.GetGuid(0);
                                element.Name = string.Format("{0}:{1}.{2}.{3}.{4}", reader.GetString(1), reader.GetInt32(2), reader.GetInt32(3), reader.GetInt32(4), reader.GetInt32(5));
                                element.EntityClass = DTO.EntityClass.OperationVersion;
                                opVerList.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return opVerList;
        }

        public static DTO.OperationVersionList ListOperationVersionsForResource(Guid ResourceID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.OperationVersionList list = new DTO.OperationVersionList();
            const string sprocname = "[ServiceEngine].[ListOperationVersionsByResource]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ResourceID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ResourceID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.OperationVersions = new List<DTO.OperationVersionListElement>();
                            while (reader.Read())
                            {
                                DTO.OperationVersionListElement element = new DTO.OperationVersionListElement();
                                element.Version = new DTO.VersionNumber();
                                element.ID = reader.GetGuid(0);
                                DTO.VersionNumber verNumber = new DTO.VersionNumber();
                                verNumber.Major = reader.GetInt32(1).ToString();
                                verNumber.Minor = reader.GetInt32(2).ToString();
                                verNumber.Build = reader.GetInt32(3).ToString();
                                verNumber.Qfe = reader.GetInt32(4).ToString();
                                element.Version = verNumber;
                                list.OperationVersions.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.OperationVersion UpdateOperationVersion(DTO.OperationVersion operationVersion, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            Guid policyID = Guid.Empty;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateOperationVersion]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter VersionMajorParam = new SqlParameter("@VersionMajor", SqlDbType.Int);
                    SqlParameter VersionMinorParam = new SqlParameter("@VersionMinor", SqlDbType.Int);
                    SqlParameter VersionBuildParam = new SqlParameter("@VersionBuild", SqlDbType.Int);
                    SqlParameter VersionQFEParam = new SqlParameter("@VersionQFE", SqlDbType.Int);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter AutoResponseParam = new SqlParameter("@AutoResponse", SqlDbType.Xml);
                    SqlParameter IsPublicParam = new SqlParameter("@IsPublic", SqlDbType.Bit);
                    SqlParameter IsActiveParam = new SqlParameter("@IsActive", SqlDbType.Bit);
                    SqlParameter IsTestableParam = new SqlParameter("@IsTestable", SqlDbType.Bit);
                    SqlParameter RequestTransformParam = new SqlParameter("@RequestTransform", SqlDbType.Xml);
                    SqlParameter ResponseTransformParam = new SqlParameter("@ResponseTransform", SqlDbType.Xml);
                    SqlParameter ResourceIDParam = new SqlParameter("@ResourceID", SqlDbType.UniqueIdentifier);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = operationVersion.ID;
                    VersionMajorParam.Value = int.Parse(operationVersion.Version.Major);
                    VersionMinorParam.Value = int.Parse(operationVersion.Version.Minor);
                    VersionBuildParam.Value = int.Parse(operationVersion.Version.Build);
                    VersionQFEParam.Value = int.Parse(operationVersion.Version.Qfe);
                    DescriptionParam.Value = operationVersion.Description;
                    if (operationVersion.AutoResponse != null)
                    {
                        AutoResponseParam.Value = operationVersion.AutoResponse.OuterXml;
                    }
                    else
                    {
                        AutoResponseParam.Value = DBNull.Value;
                    }
                    IsPublicParam.Value = operationVersion.IsPublic;
                    IsActiveParam.Value = operationVersion.IsActive;
                    IsTestableParam.Value = operationVersion.IsTestable;
                    if (operationVersion.RequestTransformXSLT != null)
                    {
                        RequestTransformParam.Value = operationVersion.RequestTransformXSLT.OuterXml;
                    }
                    else
                    {
                        RequestTransformParam.Value = DBNull.Value;
                    }
                    if (operationVersion.ResponseTransformXSLT != null)
                    {
                        ResponseTransformParam.Value = operationVersion.ResponseTransformXSLT.OuterXml;
                    }
                    else
                    {
                        ResponseTransformParam.Value = DBNull.Value;
                    }
                    if (operationVersion.Resource != null)
                    {
                        if (operationVersion.Resource.ID.Equals(Guid.Empty))
                        {
                            ResourceIDParam.Value = Resource.CreateResource(operationVersion.Resource, txn, conn);
                        }
                        else
                        {
                            ResourceIDParam.Value = operationVersion.Resource.ID;
                        }
                    }
                    else
                    {
                        ResourceIDParam.Value = null;
                        //throw new ApplicationException("CreateOperationVersion requires ChannelType, but no ChannelType was supplied");
                    }

                    if (operationVersion.Policy != null)
                    {
                        PolicyIDParam.Value = operationVersion.Policy.ID;
                    }
                    else
                    {
                        PolicyIDParam.Value = DBNull.Value;
                    }     

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(VersionMajorParam);
                    cmd.Parameters.Add(VersionMinorParam);
                    cmd.Parameters.Add(VersionBuildParam);
                    cmd.Parameters.Add(VersionQFEParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(AutoResponseParam);
                    cmd.Parameters.Add(IsPublicParam);
                    cmd.Parameters.Add(IsActiveParam);
                    cmd.Parameters.Add(IsTestableParam);
                    cmd.Parameters.Add(RequestTransformParam);
                    cmd.Parameters.Add(ResponseTransformParam);
                    cmd.Parameters.Add(ResourceIDParam);
                    cmd.Parameters.Add(PolicyIDParam);

                    cmd.ExecuteNonQuery();
                    ID = (Guid)IDParam.Value;
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return operationVersion;
        }

        public static void RemoveAssociatedOperationVersionFromEndpoint(Guid operationVersionID, Guid endpointID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemoveAssociatedOperationFromEndpoint]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter OperationVersionIDParam = new SqlParameter("@OperationVersionID", SqlDbType.UniqueIdentifier);
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    OperationVersionIDParam.Value = operationVersionID;
                    cmd.Parameters.Add(OperationVersionIDParam);
                    EndpointIDParam.Value = endpointID;
                    cmd.Parameters.Add(EndpointIDParam);

                    cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static void RemovePublishedOperationVersionFromEndpoint(Guid operationVersionID, Guid endpointID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemovePublishedOperationFromEndpoint]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter OperationVersionIDParam = new SqlParameter("@OperationVersionID", SqlDbType.UniqueIdentifier);
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    OperationVersionIDParam.Value = operationVersionID;
                    cmd.Parameters.Add(OperationVersionIDParam);
                    EndpointIDParam.Value = endpointID;
                    cmd.Parameters.Add(EndpointIDParam);

                    cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static void AssignPublishedOperationVersionToEndpoint(Guid operationVersionID, Guid endpointID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[AssignPublishedOperationVersionToEndpoint]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter OperationVersionIDParam = new SqlParameter("@OperationVersionID", SqlDbType.UniqueIdentifier);
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    OperationVersionIDParam.Value = operationVersionID;
                    cmd.Parameters.Add(OperationVersionIDParam);
                    EndpointIDParam.Value = endpointID;
                    cmd.Parameters.Add(EndpointIDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static void AssignAssociatedOperationVersionToEndpoint(Guid operationVersionID, Guid endpointID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[AssignAssociatedOperationVersionToEndpoint]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                //Note: No change tracking needed for newly activated operations at an endpoint
                //brokers will query for the operation to resolve since it won't be in the broker cache

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter OperationVersionIDParam = new SqlParameter("@OperationVersionID", SqlDbType.UniqueIdentifier);
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    OperationVersionIDParam.Value = operationVersionID;
                    cmd.Parameters.Add(OperationVersionIDParam);
                    EndpointIDParam.Value = endpointID;
                    cmd.Parameters.Add(EndpointIDParam);

                    cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        #region Private functions

        private static void SetVersionParams(string versionString, SqlParameter VersionMajorParam, SqlParameter VersionMinorParam, SqlParameter VersionBuildParam, SqlParameter VersionQFEParam)
        {
            string[] versionNodes = versionString.Split(new char[] { '.' });

            try
            {
                if (versionNodes.Length > 0) VersionMajorParam.Value = int.Parse(versionNodes[0]);
                if (versionNodes.Length > 1) VersionMinorParam.Value = int.Parse(versionNodes[1]);
                if (versionNodes.Length > 2) VersionBuildParam.Value = int.Parse(versionNodes[2]);
                if (versionNodes.Length > 3) VersionQFEParam.Value = int.Parse(versionNodes[3]);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Attempt to parse version number from Action failed: " + ex.Message);
            }
        }

        #endregion
    }
}
